﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LotteryVote.Entity;
using System.Data.SqlClient;
using LotteryVote.Utility;
using System.Text.RegularExpressions;

namespace LotteryVote.Data
{
    public class DropWaterDataAccess : DataBase
    {
        public void Insert(DropWater dropWater)
        {
            string sql = string.Format(@"INSERT INTO {0} ({1},{2},{3},{4},{5}) VALUES (@{1},@{2},@{3},@{4},@{5}) SELECT IDENT_CURRENT('{0}')",
                DropWater.TABLENAME, DropWater.NUM, DropWater.GAMEPLAYWAYID, DropWater.DROPVALUE, DropWater.AMOUNT, DropWater.DROPTYPE);
            object id = base.ExecuteScalar(sql, new SqlParameter(DropWater.NUM, dropWater.Num),
                new SqlParameter(DropWater.GAMEPLAYWAYID, dropWater.GamePlayWayId),
                new SqlParameter(DropWater.DROPVALUE, dropWater.DropValue),
                new SqlParameter(DropWater.AMOUNT, dropWater.Amount),
                new SqlParameter(DropWater.DROPTYPE, (int)dropWater.DropType));
            dropWater.DropId = Convert.ToInt32(id);
        }

        public void Insert(DropWater dropWater, LotteryCompany company)
        {
            string sql = string.Format(@"INSERT INTO {0} ({1},{2}) VALUES (@{1},@{2})", DropInCompany.TABLENAME, DropInCompany.DROPID, DropInCompany.COMPANYID);
            base.ExecuteNonQuery(sql, new SqlParameter(DropInCompany.DROPID, dropWater.DropId),
                new SqlParameter(DropInCompany.COMPANYID, company.CompanyId));
        }
        public void Delete(int dropId)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1}", DropWater.TABLENAME, DropWater.DROPID);
            base.ExecuteNonQuery(sql, new SqlParameter(DropWater.DROPID, dropId));
        }

        public DropWater GetManualDropWater(string num, int gameplayWayId, decimal amount)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1} AND {2}=@{2} AND {3}=@{3} AND {4}=@{4}", DropWater.TABLENAME, DropWater.NUM,
                DropWater.GAMEPLAYWAYID, DropWater.AMOUNT, DropWater.DROPTYPE);
            return base.ExecuteEntity<DropWater>(sql, new SqlParameter(DropWater.NUM, num),
                new SqlParameter(DropWater.GAMEPLAYWAYID, gameplayWayId),
                new SqlParameter(DropWater.AMOUNT, amount),
                new SqlParameter(DropWater.DROPTYPE, DropType.Manual));
        }
        public IList<DropWater> ListDropWater(string num, DateTime date)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1} AND DAY({2})=DAY(@{2})", DropWater.TABLENAME, DropWater.NUM, DropWater.CREATETIME);
            return base.ExecuteList<DropWater>(sql, new SqlParameter(DropWater.NUM, num),
                new SqlParameter(DropWater.CREATETIME, date));
        }
        public IList<DropWater> ListDropWater(string num, LotteryCompany company, DateTime date)
        {
            string sql = string.Format(@"select dw.* from {0} dw 
inner join {1} dc on dc.{2}=dw.{3}
where dc.{4}=@{4} and DAY(dw.{5})=DAY(@{5}) AND dw.{6}=@{6}", DropWater.TABLENAME, DropInCompany.TABLENAME, DropInCompany.DROPID, DropWater.DROPID,
                                                        DropInCompany.COMPANYID, DropWater.CREATETIME, DropWater.NUM);
            return base.ExecuteList<DropWater>(sql, new SqlParameter(DropInCompany.COMPANYID, company.CompanyId),
                new SqlParameter(DropWater.CREATETIME, date),
                new SqlParameter(DropWater.NUM, num));
        }
        public int CountAutoDropWater(int numLen, decimal amount)
        {
            string sql = string.Format(@"SELECT COUNT(0) FROM {0} WHERE {1}=@{1} AND LEN({2})=@{2} AND {3}=@{3}", DropWater.TABLENAME,
                DropWater.DROPTYPE, DropWater.NUM, DropWater.AMOUNT);
            object count = base.ExecuteScalar(sql, new SqlParameter(DropWater.DROPTYPE, (int)DropType.Auto),
                new SqlParameter(DropWater.NUM, numLen),
                new SqlParameter(DropWater.AMOUNT, amount));
            return Convert.ToInt32(count);
        }
        public int CountDropWater(int dropId, int companyId)
        {
            string sql = string.Format(@"SELECT COUNT(0) FROM {0} WHERE {1}=@{1} AND {2}=@{2}", DropInCompany.TABLENAME, DropInCompany.DROPID, DropInCompany.COMPANYID);
            object count = base.ExecuteScalar(sql, new SqlParameter(DropInCompany.DROPID, dropId),
                new SqlParameter(DropInCompany.COMPANYID, companyId));
            return Convert.ToInt32(count);
        }
        public IList<DropWater> ListAutoDropWater(int numLen)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1} AND LEN({2})=@{2}", DropWater.TABLENAME, DropWater.DROPTYPE, DropWater.NUM);
            return base.ExecuteList<DropWater>(sql, new SqlParameter(DropWater.DROPTYPE, (int)DropType.Auto),
                new SqlParameter(DropWater.NUM, numLen));
        }
        /// <summary>
        /// 列出指定号码的跌水设置.
        /// </summary>
        /// <param name="num">号码.</param>
        /// <param name="gameplaywayId">玩法.</param>
        /// <param name="companyId">公司.</param>
        /// <param name="date">日期.</param>
        /// <returns></returns>
        public IList<DropWater> ListNumsDropWater(string num, int gameplaywayId, int companyId, DateTime date)
        {
            string sql = string.Format(@"select * from {0} dw
left join {1} dc on dc.{2}=dw.{3}
where ((dc.{4}=@{4} and DAY(dw.{5})=DAY(@{5})) 
or dw.{6}=@{6}) and dw.{7}=@{7} and dw.{8}=@{8}", DropWater.TABLENAME, DropInCompany.TABLENAME, DropInCompany.DROPID, DropWater.DROPID,
                   DropInCompany.COMPANYID, DropWater.CREATETIME, DropWater.DROPTYPE, DropWater.NUM, DropWater.GAMEPLAYWAYID);
            return base.ExecuteList<DropWater>(sql, new SqlParameter(DropInCompany.COMPANYID, companyId),
                new SqlParameter(DropWater.CREATETIME, date),
                new SqlParameter(DropWater.DROPTYPE, DropType.Auto),
                new SqlParameter(DropWater.NUM, num),
                new SqlParameter(DropWater.GAMEPLAYWAYID, gameplaywayId));
        }
        public IList<DropWater> ListDropWater(DropType dropType, int pageItemCount, int pageIndex, DateTime? date = null)
        {
            int fromCount = (pageIndex - 1) * pageItemCount;
            int toCount = pageIndex * pageItemCount;
            string dateCondition = date.HasValue ? string.Format(@" AND DAY({0})=DAY(@{0})", DropWater.CREATETIME) : string.Empty;

            string sql = string.Format(@";WITH CTE AS(select  dw.*,dic.CompanyId from {0} dw
left join {1} dic on dic.{2}=dw.{3}
where dw.{4}=@{4} {9})
SELECT * FROM 
(
     SELECT ROW_NUMBER() OVER(ORDER BY {3} desc) AS RowNumber,* 
     FROM CTE
) T
WHERE RowNumber BETWEEN {7} AND {8} order by {6} desc", DropWater.TABLENAME, DropInCompany.TABLENAME, DropInCompany.DROPID, DropWater.DROPID,
                           DropWater.DROPTYPE, DropWater.CREATETIME, DropWater.AMOUNT, fromCount, toCount, dateCondition);

            var paramArr = new SqlParameter[date.HasValue ? 2 : 1];
            paramArr[0] = new SqlParameter(DropWater.DROPTYPE, (int)dropType);
            if (date.HasValue)
                paramArr[1] = new SqlParameter(DropWater.CREATETIME, date);

            return base.ExecuteList<DropWater>(sql, paramArr);
        }
        public IList<DropWater> ListDropWaterByCondition(DropType dropType, DateTime? date, string num, int companyId,
            int gameplaywayId, double dropvalue, decimal amount, int pageItemCount, int pageIndex)
        {
            string condition;
            var parameterList = BuildCondition(dropType, date, num, companyId, gameplaywayId, dropvalue, amount, out condition);

            int fromCount = (pageIndex - 1) * pageItemCount;
            int toCount = pageIndex * pageItemCount;

            string sql = string.Format(@";WITH CTE AS(select  dw.*,dic.CompanyId from {0} dw
left join {1} dic on dic.{2}=dw.{3}
where {4})
SELECT * FROM 
(
     SELECT ROW_NUMBER() OVER(ORDER BY {3} desc) AS RowNumber,* 
     FROM CTE
) T
WHERE RowNumber BETWEEN {6} AND {7} order by {5} desc", DropWater.TABLENAME, DropInCompany.TABLENAME, DropInCompany.DROPID, DropWater.DROPID,
                           condition.ToString(),DropWater.AMOUNT, fromCount, toCount);
            return base.ExecuteList<DropWater>(sql, parameterList.ToArray());
        }

        public int CountDropWater(DropType dropType, DateTime? date = null)
        {
            string dateCondition = date.HasValue ? string.Format(@" AND DAY({0})=DAY(@{0})", DropWater.CREATETIME) : string.Empty;
            string sql = string.Format(@"SELECT COUNT(0) FROM {0} WHERE {1}=@{1} {2}", DropWater.TABLENAME, DropWater.DROPTYPE, dateCondition);

            var paramArr = new SqlParameter[date.HasValue ? 2 : 1];
            paramArr[0] = new SqlParameter(DropWater.DROPTYPE, (int)dropType);
            if (date.HasValue)
                paramArr[1] = new SqlParameter(DropWater.CREATETIME, date);
            object count = base.ExecuteScalar(sql, paramArr);
            return Convert.ToInt32(count);
        }
        public int CountDropWaterByCondition(DropType dropType, DateTime? date, string num, int companyId,
            int gameplaywayId, double dropvalue, decimal amount)
        {
            string condition;
            var parameterList = BuildCondition(dropType, date, num, companyId, gameplaywayId, dropvalue, amount, out condition);
            string sql = string.Format(@"SELECT COUNT(0) FROM {0} dw
LEFT JOIN {2} dic ON dic.{3}=dw.{4}
WHERE {1}", DropWater.TABLENAME, condition, DropInCompany.TABLENAME, DropInCompany.DROPID, DropWater.DROPID);
            object count = base.ExecuteScalar(sql, parameterList.ToArray());
            return Convert.ToInt32(count);
        }

        private IList<SqlParameter> BuildCondition(DropType dropType, DateTime? date, string num, int companyId,
            int gameplaywayId, double dropvalue, decimal amount, out string condit)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            StringBuilder condition = new StringBuilder();
            Action<string, object> appendToCondtion = (it, arg) =>
            {
                if (condition.Length > 0)
                    condition.Append(" AND ");
                condition.AppendFormat("{0}=@{0}", it);
                parameterList.Add(new SqlParameter(it, arg));
            };
            if (!string.IsNullOrEmpty(num))
            {
                if (num.IsNum())
                    appendToCondtion(DropWater.NUM, num);
                else if (new Regex(@"^-+\d+$").IsMatch(num))
                {
                    if (condition.Length > 0)
                        condition.Append(" AND ");
                    int numIndex = num.LastIndexOf("-") + 1;
                    string n = num.Substring(numIndex);
                    string placeHolder = string.Empty;
                    for (int i = 0; i < num.Length - n.Length; i++)
                        placeHolder += "_";
                    condition.AppendFormat("{0} like @{0}", DropWater.NUM);
                    parameterList.Add(new SqlParameter(DropWater.NUM, placeHolder + n));
                }
            }
            if (date.HasValue)
            {
                if (condition.Length > 0)
                    condition.Append(" AND ");
                condition.AppendFormat("DAY({0})=DAY(@{0})", DropWater.CREATETIME);
                parameterList.Add(new SqlParameter(DropWater.CREATETIME, date));
            }
            if (companyId > 0)
                appendToCondtion(DropInCompany.COMPANYID, companyId);
            if (gameplaywayId > 0)
                appendToCondtion(DropWater.GAMEPLAYWAYID, gameplaywayId);
            if (dropvalue > 0)
                appendToCondtion(DropWater.DROPVALUE, dropvalue);
            if (amount > 0)
                appendToCondtion(DropWater.AMOUNT, amount);
            appendToCondtion(DropWater.DROPTYPE, (int)dropType);
            condit = condition.ToString();
            return parameterList;
        }
    }
}
